﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Using_List')
	BEGIN
		DROP  Procedure  usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Using_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
**	Name : usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Using_List
**	Desc : 연간차량비용내역 - 주유,정비,주차,통행료를 연도별로 출력
**	Test Exec Query : Exec usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Using_List 'CAR01', '2012-08', 'ko'
**	Called by : Car_Dac_UPDMS_CAR_REPORT_Car2013r.cs
**	Program ID : Car2013r
**	Auth : 송시명
**	Date : 2012-08-28
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Using_List]
@ls_car_id nvarchar(5),
@ls_base_ym nvarchar(7),
@ls_lang_set nvarchar(2)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
;
WITH rslt AS (
--주유
 SELECT Refuelling_Dt AS Biz_Dt,
        '<span class="sicon_refuelling"></span><span title="' + Remark + '">'
        + ucs.Station_Nm + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>' AS Contents
   FROM UPDMS_CAR_REFUELLING AS ucr WITH(NOLOCK)
   JOIN UPDMS_CAR_STATION AS ucs WITH(NOLOCK) ON ucr.Station_Id = ucs.Station_Id
  WHERE Car_Id = @ls_car_id
    AND LEFT(Refuelling_Dt, 4) = LEFT(@ls_base_ym, 4)
 UNION ALL
 --정비
 SELECT Service_Dt,
        '<span class="sicon_service"></span><span title="' + Remark + '">'
        + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
   FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
  WHERE Car_Id = @ls_car_id
    AND Div <> 'AD003'
    AND LEFT(Service_Dt, 4) = LEFT(@ls_base_ym, 4)
 UNION ALL
 --주차,통행료(카드)
 SELECT Biz_Dt,
        '<span class="sicon_traffic"></span><span title="' + Remark + '">'
        + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
   FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
  WHERE Account = 'AC024' 
    AND LEFT(Biz_Dt, 4) = LEFT(@ls_base_ym, 4)
 UNION ALL
 --주차,통행료(현금)
 SELECT Reg_Dt,
        '<span class="sicon_traffic"></span><span title="' + Remark + '">'
        + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
   FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
  WHERE Account = 'AC024'
    AND LEFT(Reg_Dt, 4) = LEFT(@ls_base_ym, 4)
UNION ALL
 --세차
 SELECT Service_Dt,
        '<span class="sicon_washing"></span><span title="' + Remark + '">'
        + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
   FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
  WHERE Car_Id = @ls_car_id
    AND Div = 'AD003'
    AND LEFT(Service_Dt, 4) = LEFT(@ls_base_ym, 4)
)
SELECT CASE LEFT(D,1) WHEN '0' THEN RIGHT(D,1)
                      ELSE D
       END D,
       ISNULL([M01], '') AS M01,
       ISNULL([M02], '') AS M02,
       ISNULL([M03], '') AS M03,
       ISNULL([M04], '') AS M04,
       ISNULL([M05], '') AS M05,
       ISNULL([M06], '') AS M06,
       ISNULL([M07], '') AS M07,
       ISNULL([M08], '') AS M08,
       ISNULL([M09], '') AS M09,
       ISNULL([M10], '') AS M10,
       ISNULL([M11], '') AS M11,
       ISNULL([M12], '') AS M12
  FROM
     (
       SELECT 'M'+RIGHT(umd.Yyyy_Mm, 2) AS Month_Id,
              RIGHT(umd.Dt, 2) AS D,
              ISNULL(z.Contents, '&nbsp;') AS Contents
         FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
         LEFT OUTER JOIN
            (
               SELECT DISTINCT Yyyy_Mm,
                      Dt,
                      REPLACE(REPLACE(Contents, '&lt;', '<'),'&gt;', '>') AS Contents
                 FROM
                    (
                      SELECT umd.Yyyy_Mm,
                             umd.Dt,
                             ( SELECT LTRIM(Contents) + '<br>' AS [text()]
                                 FROM rslt
                                WHERE rslt.Biz_Dt = rslt2.Biz_Dt FOR XML PATH('')
                              ) AS Contents
                        FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
                        JOIN rslt AS rslt2 WITH(NOLOCK) ON umd.Dt = rslt2.Biz_Dt
                       WHERE umd.Yyyy_Mm >= LEFT(@ls_base_ym, 4) + '-01'
                         AND umd.Yyyy_Mm <= LEFT(@ls_base_ym, 4) + '-12'
                    ) AS rslt
            ) z
           ON umd.Dt = z.Dt
        WHERE umd.Yyyy_Mm >= LEFT(@ls_base_ym, 4) + '-01'
          AND umd.Yyyy_Mm <= LEFT(@ls_base_ym, 4) + '-12'
     ) AS pv
 PIVOT ( MAX(pv.Contents) FOR pv.Month_Id IN ([M01],[M02],[M03],[M04],[M05],[M06],[M07],[M08],[M09],[M10],[M11],[M12])
       ) AS pvt

GO
